<?php

class Application_Model_DbTable_Materiels extends Zend_Db_Table_Abstract
{

	protected $_name = 'materiels';

	public function getMateriel($id)
	{
		$id = (int)$id;
		$row = $this->fetchRow('id = ' . $id);
		if (!$row) {
			throw new Exception("Impossible de trouver le materiel $id");
		}
		return $row->toArray();
	}

	public function setBrokenMateriel($id)
	{
		$data = array(
            'etat' => 'broken' ,
			 'dateRebut' => date("Y-m-d")
		);
		$this->update($data, 'id = '. (int)$id);
	}



	public function addMateriel($nom,$type,$modele,$idReseau,$numeroPoste,$dateAchat,$prix)
	{
		$data = array(
			
        	'nom'=> $nom,
        	'type' => $type,
        	'modele'=>$modele,
        	'idReseau' => $idReseau,
        	'numeroPoste' => $numeroPoste, 
        	'dateAchat' => $dateAchat,
			'prix' => $prix,          	
			

		);
		$this->insert($data);
	}

	public function updateMateriel($id,$nom,$type,$modele,$idReseau,$numeroPoste,$dateAchat,$prix)
	{
		$data = array(
            'nom'=> $nom,
        	'type' => $type,
        	'modele'=>$modele,
        	'idReseau' => $idReseau,
        	'numeroPoste' => $numeroPoste, 
        	'dateAchat' => $dateAchat,
			'prix' => $prix,    
		);
		$this->update($data, 'id = '. (int)$id);
	}

	public  function listOnServiceMateriel(){
		$select = $this->select()->setIntegrityCheck(false)
		->from(array ('m' => 'materiels'))
		->joinInner(array ('r' => 'reseaux'),'m.idReseau = r.id', array('nomReseau' => 'nom','raccourciReseau' => 'raccourci'))
		->where('m.etat = ?','on service')
		->order('m.dateAchat DESC');

			
		$r= $this->fetchAll($select);


		return $r;
	}

	public  function listMateriel(){
		$select = $this->select()->setIntegrityCheck(false)
		
		->from(array ('m' => 'materiels'))
		->joinInner(array ('r' => 'reseaux'),'m.idReseau = r.id', array('nomReseau' => 'nom','raccourciReseau' => 'raccourci'))
		
		->order('m.dateAchat DESC');
			
		//echo $select;
			
		$r= $this->fetchAll($select);


		return $r;
	}

	public  function nbFailureMateriel(){
		$select = $this->select()->setIntegrityCheck(false)
		->from(array ('m' => 'materiels'),array('nbMateriels'=>'COUNT(DISTINCT m.id)'))
		->joinInner(array ('i' => 'incidents'),'i.idMateriel = m.id', array())
		->joinInner(array ('s' => 'situations'),'i.idSituation = s.id',array())
		->where('s.nom = ?','En cours')
		->orwhere('s.nom = ?','Non traite');


			

			
		$r= $this->fetchAll($select);
		$nombre=intval( $r[0]['nbMateriels']);

		return $nombre;
	}
	


	public  function nbTotalMateriel(){
		$select = $this->select()->setIntegrityCheck(false)
		->from(array ('m' => 'materiels'),array('nbMateriels'=>'COUNT(m.id)'));



			

			
		$r= $this->fetchAll($select);
		$nombre=intval( $r[0]['nbMateriels']);

		return $nombre;
	}

	public  function statCompareCoutMateriel(){
		$select = $this->select()->setIntegrityCheck(false)
		->from(array ('m' => 'materiels'),array('nomMateriel'=>'m.nom','numeroPoste'=>'m.numeroPoste','prixPoste' => 'm.prix'))
		->joinInner(array ('i' => 'incidents'),'i.idMateriel = m.id', array('sommeIncidents'=>'SUM(i.cout)'))
		->joinInner(array ('r' => 'reseaux'),'m.idReseau = r.id', array('raccourciReseau' => 'r.raccourci'))
		->group('m.id');

			
		$r= $this->fetchAll($select);
		return $r;
	}

}

